--3.Cap nhat sodu cho TKA,trong khi do KH A kiem tra sodu(TaiKhoanThe)
if exists (select name from sysobjects where name like 'Proc_GiaoDich_RutTien')
	drop procedure Proc_GiaoDich_RutTien
go

create PROC Proc_GiaoDich_RutTien (@maTKThe int ,@loaiGD int , @ngayGD datetime ,
	 @diaChi nvarchar (50), @soTienGD float , @noiDung nvarchar,@maNV int, @trangThai int)
as
begin
	insert into GiaoDich(MaTKThe,LoaiGD,NgayGD,DiaChiGD,SoTienGD,NoiDungGD ,MaNVGD,TrangThaiGD)
	values (@maTKThe ,@loaiGD  , @ngayGD , @diaChi , @soTienGD  , @noiDung, @maNV, @trangThai) 
end
go
------------
if exists (select name from sysobjects where name like 'Proc_TaiKhoanThe_UpdateSodu')
	drop procedure Proc_TaiKhoanThe_UpdateSodu
go
create PROC Proc_TaiKhoanThe_UpdateSodu (@maTKThe int,@soDu float, @type int)
as
begin
	if(@type=1)--DirtyRead
	begin
		begin tran
			Update TaiKhoanThe 
			set SoDu= SoDu-@soDu
			where MaTaiKhoanThe= @maTKThe 
			waitfor delay '00:00:5'
			rollback tran
	end
	else if(@type=3)--unrepeatable read
	begin
		begin tran
			Update TaiKhoanThe set SoDu=SoDu-@sodu 
			where MaTaiKhoanThe=@maTKThe
		commit tran
	end
end
go

begin tran
	Update TaiKhoanThe set SoDu=SoDu + 300
	where MaTaiKhoanThe=1
commit tran

-----------
if exists (select name from sysobjects where name like 'Proc_TaiKhoanThe_GetByMaTaiKhoanChinh')
	drop procedure Proc_TaiKhoanThe_GetByMaTaiKhoanChinh
go

create PROC Proc_TaiKhoanThe_GetByMaTaiKhoanChinh (@maTK int, @type int)
as
begin
	if(@type=1)--Dirty Read
	begin
		begin tran
		set tran isolation level read uncommitted
				select * from TaiKhoanThe
				where MaTkChinh = @maTK
		commit tran
	end
	else if(@type=2)--phantom
	begin
		begin tran
		set transaction isolation level repeatable read
			select * from TaiKhoanThe where MaTkChinh=@maTK
			waitfor delay '00:00:10'
			select * from TaiKhoanThe where MaTkChinh=@maTK
		commit tran
	end
	else if(@type=3)--Unrepeatable
	begin
		begin tran
			waitfor delay '00:00:10'
			select * from TaiKhoanThe where MaTkChinh = @maTK
		commit tran
	end
end
go
----------
if exists (select name from sysobjects where name like 'Proc_TaiKhoanThe_Insert')
	drop procedure Proc_TaiKhoanThe_Insert
go

create PROC Proc_TaiKhoanThe_Insert 
(@maTKC int, @sodu float, @laisuat float,@maloaithe int,@maloaitien int, @maloaiTK int, @maKH int,@mapin nchar)
as
declare @maTKThe int
	insert into TaiKhoanThe(MaTkChinh,SoDu,LaiSuat,MaLoaiThe,LockFlg,DelFlg,MaLoaiTien,MaLoaiTK)
	values(@maTKC, @sodu, @laisuat, @maloaithe, 0, 0, @maloaitien,@maloaiTK)
	
	set @maTKThe = (select top(1)MaTaiKhoanThe from TaiKhoanThe order by MaTaiKhoanThe desc)

	insert into ChiTietThe(MaTKThe,MaSoKH,NgayTao,LockFlg,DelFlg,MaSoPin)
	values(@maTKThe, @maKH, getdate(), 0, 0,@mapin)
	select @maTKThe
go
----------------------
if exists (select name from sysobjects where name like 'Proc_LoaiTaiKhoan_TKThe')
	drop procedure Proc_LoaiTaiKhoan_TKThe
go

create PROC Proc_LoaiTaiKhoan_TKThe
as
begin
	select * from LoaiTaiKhoan where DelFlg=0 and TKTheFlg=1
end
go
-----------------
if exists (select name from sysobjects where name like 'Proc_TaiKhoanChinh_GetByMaKH')
	drop procedure Proc_TaiKhoanChinh_GetByMaKH
go

create PROC Proc_TaiKhoanChinh_GetByMaKH (@maKH int )
as
	select * from TaiKhoanChinh
	where MaKH= @maKH
go
------------
